/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Server.ImplementInterface;

import DAL.DBManager;
import Server.DTO.SavingType_DTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author 10520_000
 */
public class SavingTypeRepository {

    public static int Insert(SavingType_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "INSERT INTO SAVINGTYPE(TypeID, TypeName, Term, InterestRate, AllowSaver, AllowWithdraw) Values(?,?,?,?,?,?)";
            PreparedStatement pstmt = con.prepareStatement(SQL);

            pstmt.setString(1, info.getTypeID());
            pstmt.setString(2, info.getName());
            pstmt.setInt(3, info.getTerm());
            pstmt.setFloat(4, info.getInterestRate());
            pstmt.setBoolean(5, info.getAllowSave());
            pstmt.setBoolean(6, info.getAllowWithdraw());
            iRet = pstmt.executeUpdate();

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    //
    public static int Update(SavingType_DTO info) throws SQLException {
        Connection con = DBManager.getInstance().getConnection();
        String sql = "Update SavingType set "
                + "TypeName='" + info.getName() + "',"
                + "Term=" + info.getTerm() + ","
                + "InterestRate=" + info.getInterestRate() + ","
                + "AllowSaver='" + info.getAllowSave() + "',"
                + "AllowWithDraw='" + info.getAllowWithdraw() + "' "
                + "where TypeID='" + info.getTypeID() + "'";

        return con.createStatement().executeUpdate(sql);
    }

    public static int Delete(SavingType_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "DELETE SAVINGTYPE WHERE TypeID= ?";
            PreparedStatement pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, info.getTypeID());

            iRet = pstmt.executeUpdate();

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return iRet;
    }

    public static void DeleteAll() {
        Connection con = DBManager.getInstance().getConnection();
        try {
            con.setAutoCommit(false);
            String SQL = "DELETE FROM SAVINGTYPE";
            PreparedStatement pstmt = con.prepareStatement(SQL);

            pstmt.executeUpdate();
            con.commit();
        } catch (SQLException se) {
            try {
                con.rollback();
            } catch (SQLException ise) {
            }
        } finally {
            try {
                con.setAutoCommit(true);
            } catch (SQLException fse) {
            }
        }
    }

    public static ArrayList FindAll() {
        ArrayList arr = new ArrayList();

        try {
            String sql = "SELECT * FROM SAVINGTYPE ORDER BY TypeID";
            Connection con = DBManager.getInstance().getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                SavingType_DTO info = new SavingType_DTO();
                info.setTypeID(rs.getString("TypeID"));
                info.setName(rs.getString("TypeName"));
                info.setAllowSave(rs.getBoolean("AllowSaver"));
                info.setInterestRate(rs.getFloat("InterestRate"));
                info.setAllowWithdraw(rs.getBoolean("AllowWithdraw"));
                info.setTerm(rs.getInt("Term"));

                arr.add(info);
            }

            stmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }

    public static SavingType_DTO FindByTypeID(String TypeID) {
        SavingType_DTO info = new SavingType_DTO();

        try {
            String sql = "SELECT * FROM SAVINGTYPE WHERE TypeID = '" + TypeID + "'";
            Connection con = DBManager.getInstance().getConnection();
            ResultSet rs = con.createStatement().executeQuery(sql);

            while (rs.next()) {
                info.setTypeID(rs.getString("TypeID"));
                info.setName(rs.getString("TypeName"));
                info.setTerm(Integer.parseInt(rs.getString("Term")));
                info.setInterestRate(Float.parseFloat(rs.getString("InterestRate")));
                info.setAllowSave(rs.getBoolean("AllowSaver"));
                info.setAllowWithdraw(rs.getBoolean("AllowWithdraw"));
            }
        } catch (SQLException se) {
            System.out.println(se);
        }
        return info;
    }

    public static String GetSavingType(String str_TypeName, int str_Term) throws SQLException {
        String result = "";
        String sql = "select * from SavingType where TypeName='" + str_TypeName + "' and Term=" + str_Term;
        Connection con = DBManager.getInstance().getConnection();
        ResultSet rs = con.createStatement().executeQuery(sql);
        while (rs.next()) {
            result = rs.getString("TypeID");
        }
        return result;
    }

    public static ArrayList GetNames() throws SQLException {
        ArrayList arr = new ArrayList();
        String sql = "select distinct TypeName from SavingType";
        Connection con = DBManager.getInstance().getConnection();
        ResultSet rs = con.createStatement().executeQuery(sql);
        while (rs.next()) {
            arr.add(rs.getString("TypeName"));
        }
        return arr;
    }

    public static boolean Check_TypeID_Exists(String str_TypeID) throws SQLException {
        boolean result = false;
        String sql = "select *\n"
                + "from SAVINGBOOK inner join SAVINGTYPE on SAVINGBOOK.TypeID=SAVINGTYPE.TypeID\n"
                + "where SAVINGTYPE.TypeID='" + str_TypeID + "'";
        Connection con = DBManager.getInstance().getConnection();
        ResultSet rs = con.createStatement().executeQuery(sql);

        if (!rs.next()) {
            result = false;
        } else {
            result = true;
        }
        return result;
    }
    
    public static ArrayList GetSavingTypes(String str_TypeName) throws SQLException
    {
        ArrayList arr = new ArrayList();
        String sql = "select * from SavingType where TypeName like '%"+str_TypeName+"%'";
        Connection con = DBManager.getInstance().getConnection();
        ResultSet rs = con.createStatement().executeQuery(sql);
        while(rs.next())
        {
            SavingType_DTO info = new SavingType_DTO();
            info.setTypeID(rs.getString("TypeID"));
            info.setName(rs.getString("TypeName"));
            info.setTerm(Integer.parseInt(rs.getString("Term")));
            info.setInterestRate(Float.parseFloat(rs.getString("InterestRate")));
            info.setAllowSave(rs.getBoolean("AllowSaver"));
            info.setAllowWithdraw(rs.getBoolean("AllowWithdraw"));
            arr.add(info);
        }
        return arr;
    }
    
    //get terms
    public static ArrayList GetTerms() throws SQLException
    {
        ArrayList arr = new ArrayList();
        String sql = "select Term from SavingType";
        Connection con = DBManager.getInstance().getConnection();
        ResultSet rs = con.createStatement().executeQuery(sql);
        while(rs.next())
        {
            arr.add(Integer.parseInt(rs.getString("Term")));
        }
        return arr;
    }
    public static void main(String[] str) throws SQLException {
        ArrayList arr = GetTerms();
        for(int i=0;i<arr.size();i++)
        {
            System.out.println(arr.get(i));
        }
    }
}
